from odoo import api, fields, models, _
from odoo.exceptions import UserError
from odoo.tools.float_utils import float_compare


class DocumentsInvoiceWizard(models.TransientModel):
    _name = "documents.invoice.wizard"
    _description = "发票生成向导"

    # 通用字段
    language_mode = fields.Selection(
        selection=[("zh", "中文"), ("en", "英文"), ("it", "意大利文")],
        string="语言",
        default="zh",
        required=True,
    )
    export_format = fields.Selection(
        selection=[("xlsx", "XLSX")],
        string="导出格式",
        default="xlsx",
        required=True,
    )
    date = fields.Date(string="日期", default=fields.Date.context_today, required=True)
    company_id = fields.Many2one(
        comodel_name="res.company",
        string="公司",
        default=lambda self: self.env.company,
        required=True,
    )
    partner_id = fields.Many2one(
        comodel_name="res.partner",
        string="客户",
        domain=[("type", "!=", "private")],
    )
    # 币种和汇率
    currency_id = fields.Many2one(
        comodel_name="res.currency",
        string="币种",
        default=lambda self: self.env.company.currency_id,
        required=True,
    )
    manual_rate = fields.Float(string="汇率", help="例如：1 EUR = 8.33 CNY，则填 8.33")
    show_manual_rate = fields.Boolean(string="显示汇率", compute="_compute_show_manual_rate")

    note = fields.Text(string="备注")
    send_email = fields.Boolean(string="导出后发送邮件")

    # 特定字段
    sale_ids = fields.Many2many("sale.order", string="销售订单", required=True)
    
    # 计算字段：自动获取销售订单的包裹数
    pallet_count = fields.Integer(string="托盘数", compute='_compute_pallet_count', store=False)
    
    incoterm_id = fields.Many2one("account.incoterms", string="贸易条款")
    invoice_no = fields.Char(string="发票号")
    
    # 目的地字段
    destination = fields.Char(string="目的地")
    
    # 客户仓库地址
    warehouse_address = fields.Text(string="仓库地址")
    
    @api.depends('sale_ids')
    def _compute_pallet_count(self):
        """计算销售订单的包裹数（支持多选）"""
        for wizard in self:
            if wizard.sale_ids:
                moves = self.env['stock.move'].search([
                    ('sale_line_id.order_id', 'in', wizard.sale_ids.ids),
                    ('state', 'in', ['confirmed', 'partially_available', 'assigned', 'done']),
                    ('picking_id.picking_type_code', '=', 'outgoing')
                ])

                unique_package_levels = set()
                for move in moves:
                    if move.picking_id and move.picking_id.package_level_ids:
                        for package_level in move.picking_id.package_level_ids:
                            unique_package_levels.add(package_level.id)
                    elif move.package_level_id:
                        unique_package_levels.add(move.package_level_id.id)

                wizard.pallet_count = len(unique_package_levels)
            else:
                wizard.pallet_count = 0

    @api.model
    def default_get(self, fields_list):
        """设置默认值"""
        res = super().default_get(fields_list)
        if 'invoice_no' in fields_list and not res.get('invoice_no'):
            # 自动生成发票编号
            sequence = self.env['ir.sequence'].search([('code', '=', 'mommy.documents.invoice')], limit=1)
            if sequence:
                res['invoice_no'] = sequence.next_by_id()
        return res

    @api.onchange("sale_ids")
    def _onchange_sale_ids(self):
        if self.sale_ids:
            partners = self.sale_ids.mapped("partner_id")
            if len(partners) == 1:
                self.partner_id = partners[0]
            else:
                return {
                    'warning': {
                        'title': _('客户不一致'),
                        'message': _('已选择不同客户的销售订单，无法合并开票。')
                    }
                }
            first_sale = self.sale_ids[0]
            self.currency_id = first_sale.currency_id or self.env.company.currency_id
            if not self.company_id:
                self.company_id = self.env.company

    @api.onchange("company_id")
    def _onchange_company_id(self):
        # 按公司重置银行账户域；如果有效，值可以保留
        if self.company_id and not self.partner_id and self.sale_ids:
            partners = self.sale_ids.mapped("partner_id")
            if len(partners) == 1:
                self.partner_id = partners[0]

    @api.onchange("currency_id", "date", "company_id")
    def _onchange_currency_or_date(self):
        # 自动填充从当前公司币种到所选币种的汇率
        if self.currency_id and self.company_id:
            company_currency = self.company_id.currency_id
            if self.currency_id != company_currency:
                # 获取从公司币种到所选币种的汇率
                rate = self.env['res.currency.rate'].search([
                    ('currency_id', '=', self.currency_id.id),
                    ('name', '<=', self.date)
                ], order='name desc', limit=1)
                if rate:
                    self.manual_rate = rate.rate
                else:
                    # 回退到当前汇率
                    self.manual_rate = self.currency_id.rate
            else:
                self.manual_rate = 1.0

    @api.depends("currency_id", "company_id")
    def _compute_show_manual_rate(self):
        for wizard in self:
            wizard.show_manual_rate = bool(
                wizard.currency_id
                and wizard.company_id
                and wizard.currency_id != wizard.company_id.currency_id
            )

    def _get_language_labels(self):
        """根据选择的语言返回标签"""
        if self.language_mode == 'zh':
            return {
                'client': '客户',
                'part_no': '产品编号',
                'description': '产品描述',
                'quantity': '数量',
                'price': '单价',
                'total': '金额',
                'vat': '税额',
                'subtotal': '小计',
                'total_amount': '含税总额',
                'invoice_no': '发票编号',
                'date': '日期',
                'currency': '币种',
                'exchange_rate': '汇率',
                'pallets': '包裹数量',
                'destination': '目的地',
                'incoterms': '贸易条款',
                'payment': '付款条件',
                'bank_name': '银行名称',
                'note': '备注',
                'vat_clause': '增值税：VAT: ART. 8 1° COMMA LETTERA A D.P.R. 633/72'
            }
        elif self.language_mode == 'it':
            return {
                'client': 'Cliente',
                'part_no': 'Codice Prodotto',
                'description': 'Descrizione',
                'quantity': 'Quantità',
                'price': 'Prezzo Unitario',
                'total': 'Importo',
                'vat': 'IVA',
                'subtotal': 'Imponibile',
                'total_amount': 'Totale',
                'invoice_no': 'N. Fattura',
                'date': 'Data',
                'currency': 'Valuta',
                'exchange_rate': 'Cambio',
                'pallets': 'Pallet',
                'destination': 'Destinazione',
                'incoterms': 'Incoterms',
                'payment': 'Pagamento',
                'bank_name': 'Nome Banca',
                'note': 'Note',
                'vat_clause': 'VAT: ART. 8 1° COMMA LETTERA A D.P.R. 633/72'
            }
        else:  # 英文
            return {
                'client': 'Client',
                'part_no': 'Part No.',
                'description': 'Description',
                'quantity': 'Quantity',
                'price': 'Unit Price',
                'total': 'Amount',
                'vat': 'VAT',
                'subtotal': 'Subtotal',
                'total_amount': 'Total',
                'invoice_no': 'Invoice No.',
                'date': 'Date',
                'currency': 'Currency',
                'exchange_rate': 'Exchange Rate',
                'pallets': 'Pallets',
                'destination': 'Destination',
                'incoterms': 'Incoterms',
                'payment': 'Payment',
                'bank_name': 'Bank Name',
                'note': 'Note',
                'vat_clause': 'VAT: ART. 8 1° COMMA LETTERA A D.P.R. 633/72'
            }

    def action_export(self):
        self.ensure_one()
        if not self.sale_ids:
            raise UserError(_("Please select a Sales Order."))

        # 校验所选销售订单客户一致性
        partners = self.sale_ids.mapped('partner_id')
        if len(partners) > 1:
            # 在导出时阻止继续，以避免错误开票
            raise UserError(_("所选销售订单的客户不一致，无法合并开具同一发票。请仅选择同一客户的销售订单。"))

        # 额外校验：如果存在已全部开票的销售订单，给出警告并阻止
        already_invoiced_orders = self.sale_ids.filtered(lambda s: getattr(s, 'invoice_status', '') == 'invoiced')
        if already_invoiced_orders:
            raise UserError(_("以下销售订单已经开过发票，无法与其它订单一起开票：\n%s") % "\n".join(already_invoiced_orders.mapped('name')))

        # 校验币种一致性，避免生成多张发票导致Excel无法合并
        currencies = self.sale_ids.mapped('currency_id')
        if len(currencies) > 1:
            raise UserError(_("所选销售订单的币种不一致，无法合并生成同一张发票并导出为一张Excel。请仅选择相同币种的订单。"))

        # 校验开票地址一致性（与原生勾选“合并账单”一致：同一客户+同一开票地址）
        invoice_partners = self.sale_ids.mapped('partner_invoice_id')
        if len(invoice_partners) > 1:
            raise UserError(_("所选销售订单的开票地址不一致，无法勾选“合并账单”生成同一张发票。请仅选择开票地址一致的订单。"))

        # 检查发票编号是否已存在
        existing_invoice = None
        if self.invoice_no:
            # 查找具有相同编号的现有发票
            existing_invoice = self.env['account.move'].search([
                ('name', '=', self.invoice_no),
                ('move_type', '=', 'out_invoice'),
                ('company_id', '=', self.company_id.id)
            ], limit=1)
        
        if existing_invoice:
            # 使用现有发票
            move = existing_invoice
            # 如果发票是草稿状态，更新其信息
            if move.state == 'draft':
                # 构建叙述：固定的VAT文本 + 可选托盘数 + 备注
                narration_parts = ["VAT: ART. 8 1° COMMA LETTERA A D.P.R. 633/72"]
                if self.pallet_count:
                    narration_parts.append(_("Pallets: %s") % self.pallet_count)
                if self.note:
                    narration_parts.append(self.note)
                narration_text = "\n".join(narration_parts)

                # 准备写入现有发票的值
                write_vals = {
                    "invoice_date": self.date,
                    "date": self.date,
                    "partner_id": self.partner_id.id if self.partner_id else move.partner_id.id,
                    "narration": narration_text,
                }
                move.write(write_vals)
                # 不回写向导币种或汇率到系统发票
        else:
            # 使用原生向导 sale.advance.payment.inv，启用合并账单 consolidated_billing
            moves_before = set(self.sale_ids.mapped('invoice_ids').ids)
            ctx = dict(self.env.context, active_model='sale.order', active_ids=self.sale_ids.ids, allowed_company_ids=[self.company_id.id])
            # 开票策略：默认按已交付数量开票，可按需调整为 'ordered'
            adv_vals = {
                'advance_payment_method': 'delivered',
                'consolidated_billing': True,
            }
            adv_wizard = self.env['sale.advance.payment.inv'].with_context(ctx).create(adv_vals)
            action = adv_wizard.create_invoices()

            created_moves = self.env['account.move']
            try:
                if isinstance(action, dict) and action.get('domain'):
                    # 原生返回动作包含新建发票的 domain
                    created_moves = self.env['account.move'].search(action['domain'])
            except Exception:
                created_moves = self.env['account.move']

            if not created_moves:
                # 回退：通过对比前后，获取新创建的发票
                moves_after = set(self.sale_ids.mapped('invoice_ids').ids)
                created_ids = list(moves_after - moves_before)
                if created_ids:
                    created_moves = self.env['account.move'].browse(created_ids)

            if not created_moves:
                raise UserError(_("The selected Sales Order has nothing to invoice."))

            # 期望合并后仅一张；若多张，选第一张用于导出
            move = created_moves[0]

        # 构建叙述：固定的VAT文本 + 可选托盘数 + 备注
        narration_parts = ["VAT: ART. 8 1° COMMA LETTERA A D.P.R. 633/72"]
        if self.pallet_count:
            narration_parts.append(_("Pallets: %s") % self.pallet_count)
        if self.note:
            narration_parts.append(self.note)
        narration_text = "\n".join(narration_parts)

        # 准备写入创建的发票的值
        write_vals = {
            "invoice_date": self.date,
            "date": self.date,
            "partner_id": self.partner_id.id if self.partner_id else move.partner_id.id,
            "narration": narration_text,
        }

        # 确保所有发票都使用向导中的发票号
        if self.invoice_no:
            write_vals["name"] = self.invoice_no

        move.write(write_vals)

        # 不回写向导币种或汇率到系统发票

        # 原生一致：不再写回 combined_invoice_id 到销售订单

        # 导出到Excel
        return self._export_to_excel(move)

    def _export_to_excel(self, invoice):
        """导出发票到Excel"""
        try:
            import xlsxwriter
            from io import BytesIO
            import base64
        except ImportError:
            raise UserError(_("Please install xlsxwriter: pip install xlsxwriter"))

        # 获取多语言标签
        labels = self._get_language_labels()

        # 创建Excel文件
        output = BytesIO()
        workbook = xlsxwriter.Workbook(output)
        
        # 设置样式
        company_header_format = workbook.add_format({
            'bold': True,
            'align': 'center',
            'valign': 'vcenter',
            'font_size': 14,
            'border': 1,
            'border_color': '#000000',
            'left_color': '#000000'
        })
        
        company_info_format = workbook.add_format({
            'align': 'center',
            'valign': 'vcenter',
            'font_size': 11,
            'border': 1,
            'border_color': '#000000',
            'left_color': '#000000'
        })

        # 头三行不需要下边框
        company_header_nobottom_format = workbook.add_format({
            'bold': True,
            'align': 'center',
            'valign': 'vcenter',
            'font_size': 14,
            'top': 1,
            'left': 1,
            'right': 1,
            'top_color': '#000000',
            'left_color': '#000000',
            'right_color': '#000000'
        })

        company_info_nobottom_format = workbook.add_format({
            'align': 'center',
            'valign': 'vcenter',
            'font_size': 11,
            'left': 1,
            'right': 1,
            'left_color': '#000000',
            'right_color': '#000000'
        })

        # VAT行不需要上边框，避免成为第三行的下边框
        company_info_notop_format = workbook.add_format({
            'align': 'center',
            'valign': 'vcenter',
            'font_size': 11,
            'left': 1,
            'right': 1,
            'bottom': 1,
            'left_color': '#000000',
            'right_color': '#000000',
            'bottom_color': '#000000'
        })
        
        date_invoice_format = workbook.add_format({
            'align': 'right',
            'font_size': 11,
            'right': 1,
            'border_color': '#000000',
            'right_color': '#000000'
        })
        
        client_label_format = workbook.add_format({
            'bold': True,
            'font_size': 11
        })
        
        client_info_format = workbook.add_format({
            'font_size': 10
        })
        
        table_header_format = workbook.add_format({
            'bold': True,
            'align': 'center',
            'valign': 'vcenter',
            'font_size': 11,
            'border': 1,
            'left_color': '#000000'
        })
        
        table_cell_format = workbook.add_format({
            'font_size': 10,
            'border': 1,
            'align': 'center',
            'left_color': '#000000'
        })
        
        table_cell_left_format = workbook.add_format({
            'font_size': 10,
            'border': 1,
            'align': 'left',
            'left_color': '#000000'
        })
        
        total_format = workbook.add_format({
            'bold': True,
            'font_size': 11,
            'align': 'right'
        })
        
        bottom_info_format = workbook.add_format({
            'font_size': 10
        })
        
        # 额外边框格式
        left_column_border_format = workbook.add_format({'left': 1, 'left_color': '#000000'})
        right_column_border_format = workbook.add_format({'right': 1, 'right_color': '#000000'})
        right_only_border_format = workbook.add_format({'right': 1, 'right_color': '#000000'})
        client_label_left_format = workbook.add_format({'bold': True, 'font_size': 11, 'left': 1, 'left_color': '#000000'})
        client_info_left_border_format = workbook.add_format({'font_size': 10, 'left': 1, 'left_color': '#000000'})
        client_info_left_right_border_format = workbook.add_format({'font_size': 10, 'left': 1, 'right': 1, 'left_color': '#000000'})
        bottom_info_left_border_format = workbook.add_format({'font_size': 10, 'left': 1, 'left_color': '#000000'})
        bottom_info_left_right_format = workbook.add_format({'font_size': 10, 'left': 1, 'right': 1, 'left_color': '#000000'})
        address_bank_border_format = workbook.add_format({'font_size': 10, 'left': 1, 'right': 1, 'bottom': 1, 'left_color': '#000000'})
        
        # 总计行格式，带右边框
        total_format_with_border = workbook.add_format({
            'bold': True,
            'font_size': 11,
            'align': 'right',
            'right': 1,
            'right_color': '#000000'
        })
        
        worksheet = workbook.add_worksheet('Invoice')
        # 关闭网格线，避免视觉上的下边框（界面与打印）
        try:
            worksheet.hide_gridlines(2)
        except Exception:
            pass
        
        # 打印设置：A4 纵向，按页宽缩放，水平居中，边距
        try:
            worksheet.set_portrait()
            worksheet.set_paper(9)  # A4
            worksheet.fit_to_pages(1, 0)
            worksheet.center_horizontally()
            worksheet.set_margins(left=0.3, right=0.3, top=0.6, bottom=0.8)
        except Exception:
            pass
        
        # 设置列宽（新增“货币”列放在数量之后）
        worksheet.set_column('A:A', 15)  # Part No
        worksheet.set_column('B:B', 35)  # Description
        worksheet.set_column('C:C', 10)  # Quantity
        worksheet.set_column('D:D', 10)  # Currency
        worksheet.set_column('E:E', 15)  # Price
        worksheet.set_column('F:F', 15)  # Total
        worksheet.set_column('G:G', 12)  # VAT
        
        # 设置页眉行高
        worksheet.set_row(0, 25)  # 第1行 - 公司名称
        worksheet.set_row(1, 20)  # 第2行 - 公司地址
        worksheet.set_row(2, 20)  # 第3行 - 城市邮编
        worksheet.set_row(3, 20)  # 第4行 - VAT和日期
        worksheet.set_row(4, 20)  # 第5行 - 发票号
        
        # 1. 顶部公司信息 (居中，跨列A-G)
        company = self.company_id
        worksheet.merge_range('A1:G1', (company.display_name or company.name), company_header_nobottom_format)
        # 第二行打印公司的详细地址（使用 partner 的 street2）
        worksheet.merge_range('A2:G2', (company.partner_id and (company.partner_id.street or '') ) or '', company_info_nobottom_format)
        # 第三行打印邮编与城市（使用 partner 的 zip/city）
        city_zip = f"{(company.partner_id and (company.partner_id.zip or '')) or ''} {(company.partner_id and (company.partner_id.city or '')) or ''} ITALIA".strip()
        worksheet.merge_range('A3:G3', city_zip, company_info_nobottom_format)
        if company.vat:
            # 多语言增值税号标签
            vat_label = {'zh': '增值税号', 'en': 'VAT', 'it': 'PARTITA IVA'}.get(self.language_mode, 'VAT')
            worksheet.merge_range('A4:G4', f"{vat_label}: {company.vat}", company_info_notop_format)
        
        # 2. 日期和发票信息 (G列，在公司信息下面)
        worksheet.write('G5', f"{labels['date'].upper()}: {self.date.strftime('%d/%m/%Y')}", date_invoice_format)
        invoice_number = self.invoice_no or invoice.name or "Draft"
        worksheet.write('G6', f"{labels['invoice_no'].upper()}: {invoice_number}", date_invoice_format)
        
        # 为日期和发票编号行添加左边框
        worksheet.write_blank('A5', None, left_column_border_format)
        worksheet.write_blank('A6', None, left_column_border_format)
        
        # 为G列设置列宽（并保持右边框）
        worksheet.set_column('G:G', 15, right_column_border_format)  # 确保G列有足够宽度并显示右边框
        
        # 添加页眉分隔线
        header_separator_format = workbook.add_format({
            'bottom': 2,  # 粗线
            'bottom_color': '#000000'
        })
        worksheet.set_row(6, 3)  # 设置第7行为分隔线行
        worksheet.merge_range('A7:G7', '', header_separator_format)
        
        # 3. 客户信息 (左侧，从第8行开始)
        client = self.partner_id or invoice.partner_id
        worksheet.write('A8', f"{labels['client'].upper()}:", client_label_left_format)
        worksheet.write_blank('G8', None, right_only_border_format)
        
        # 客户名称
        worksheet.merge_range('A9:D9', client.name, client_info_left_border_format)
        worksheet.write_blank('G9', None, right_only_border_format)
 
        # 客户详细信息（名称、详细地址附带城市省份国家、增值税号）
        row = 10
        # 详细地址 + 城市/省份/国家（同一行，逗号分隔）
        address_parts = []
        if getattr(client, 'street', False):
            address_parts.append(client.street)
        if getattr(client, 'city', False):
            address_parts.append(client.city)
        if getattr(client, 'state_id', False) and client.state_id:
            if getattr(client.state_id, 'name', False):
                address_parts.append(client.state_id.name)
        if getattr(client, 'country_id', False) and client.country_id:
            if getattr(client.country_id, 'name', False):
                address_parts.append(client.country_id.name)
        if any(address_parts):
            address_label = {'zh': '地址', 'en': 'Address', 'it': 'Indirizzo'}.get(self.language_mode, 'Address')
            worksheet.merge_range(f'A{row}:G{row}', f"{', '.join([p for p in address_parts if p])}", client_info_left_right_border_format)
            row += 1
        # 增值税号
        if client.vat:
            vat_label = {'zh': '增值税号', 'en': 'VAT', 'it': 'PARTITA IVA'}.get(self.language_mode, 'VAT')
            worksheet.merge_range(f'A{row}:D{row}', f"{client.vat}", client_info_left_border_format)
            worksheet.write_blank(f'G{row}', None, right_only_border_format)
            row += 1
 
        # 为产品编号上一行添加左边框
        worksheet.write_blank(f'A{row}', None, left_column_border_format)
        
        # 仅打印币种（不打印汇率）在产品编号上一行
        # try:
        #     current_currency = (self.currency_id or invoice.currency_id or self.company_id.currency_id)
        #     currency_code = (current_currency and current_currency.name) or ''
        #     worksheet.write(f'D{row}', f"{labels['currency'].upper()}: {currency_code}", client_info_format)
        # except Exception:
        #     pass
 
        # 4. 产品表格 (从row+1行开始)
        table_start_row = row + 1
        
        # 表格头部
        # 为表格头部创建带左边框的格式
        table_header_left_format = workbook.add_format({
            'bold': True,
            'align': 'center',
            'valign': 'vcenter',
            'font_size': 11,
            'border': 1,
            'left': 1,
            'left_color': '#000000'
        })
        worksheet.write(f'A{table_start_row}', labels['part_no'].upper(), table_header_left_format)
        worksheet.write(f'B{table_start_row}', labels['description'].upper(), table_header_format)
        worksheet.write(f'C{table_start_row}', labels['quantity'].upper(), table_header_format)
        worksheet.write(f'D{table_start_row}', labels['currency'].upper(), table_header_format)
        worksheet.write(f'E{table_start_row}', labels['price'].upper(), table_header_format)
        worksheet.write(f'F{table_start_row}', labels['total'].upper(), table_header_format)
        worksheet.write(f'G{table_start_row}', labels['vat'].upper(), table_header_format)
        
        # 产品明细
        data_row = table_start_row + 1
        subtotal = 0
        total_vat = 0
        
        # 获取展示币种与符号（优先向导币种，回退发票币种）
        display_currency = self.currency_id or invoice.currency_id
        currency_symbol = display_currency.symbol if display_currency else ''
        
        # 为产品明细第一列创建带左边框的格式
        table_cell_left_format = workbook.add_format({
            'font_size': 10,
            'border': 1,
            'align': 'center',
            'left': 1
        })
        
        # 为产品明细第一列（A列）创建专门的左边框格式
        table_cell_a_format = workbook.add_format({
            'font_size': 10,
            'border': 1,
            'align': 'center',
            'left': 1,
            'left_color': '#000000'
        })
        
        # 计算展示用汇率（默认为1.0）
        display_rate = self.manual_rate or 1.0

        for line in invoice.invoice_line_ids:
            worksheet.write(f'A{data_row}', (line.product_id.default_code or ''), table_cell_a_format)
            worksheet.write(f'B{data_row}', line.product_id.name or line.name, table_cell_left_format)
            worksheet.write(f'C{data_row}', line.quantity, table_cell_format)
            # 货币代码（优先向导币种）
            try:
                currency_code = (display_currency and display_currency.name) or ''
            except Exception:
                currency_code = ''
            worksheet.write(f'D{data_row}', currency_code, table_cell_format)
            # 单价带货币符号（按汇率换算）
            worksheet.write(f'E{data_row}', f"{currency_symbol}{(line.price_unit * display_rate):.2f}", table_cell_format)
            
            amount = line.price_subtotal * display_rate
            vat_amount = (line.price_total - line.price_subtotal) * display_rate
            # 金额带货币符号（按汇率换算）
            worksheet.write(f'F{data_row}', f"{currency_symbol}{amount:.2f}", table_cell_format)
            # 税额也带货币符号（按汇率换算）
            worksheet.write(f'G{data_row}', f"{currency_symbol}{vat_amount:.2f}", table_cell_format)
            
            subtotal += amount
            total_vat += vat_amount
            data_row += 1
        
        # 5. 总计信息 (在表格下方)
        total_row = data_row + 1
        
        # 为total invoice上一行添加左边框
        worksheet.write_blank(f'A{data_row}', None, left_column_border_format)
        
        worksheet.merge_range(f'E{total_row}:F{total_row}', "TOTAL INVOICE:", total_format_with_border)
        # 总计金额带货币符号
        worksheet.write(f'G{total_row}', f"{currency_symbol}{(subtotal + total_vat):.2f}", total_format_with_border)
        
        # 为total invoice行添加左边框
        worksheet.write_blank(f'A{total_row}', None, left_column_border_format)
        
        # 为total invoice下四行添加左边框
        for i in range(1, 5):
            worksheet.write_blank(f'A{total_row + i}', None, left_column_border_format)
        
        # 6. 底部信息 (从total_row+3开始)
        info_start_row = total_row + 3
        current_row = info_start_row
        
        # PALLET
        if self.pallet_count:
            worksheet.write(f'A{current_row}', f"PALLET:", bottom_info_left_border_format)
            worksheet.write(f'B{current_row}', str(self.pallet_count), bottom_info_left_border_format)
            worksheet.write_blank(f'G{current_row}', None, right_only_border_format)
            current_row += 1
        
        # DESTINATION
        if self.destination:
            worksheet.write(f'A{current_row}', f"DESTINATION:", bottom_info_left_border_format)
            worksheet.write(f'B{current_row}', self.destination.upper(), bottom_info_left_border_format)
            worksheet.write_blank(f'G{current_row}', None, right_only_border_format)
            current_row += 1
        
        # INCOTERMS
        if self.incoterm_id:
            worksheet.write(f'A{current_row}', f"INCOTERMS:", bottom_info_left_border_format)
            worksheet.write(f'B{current_row}', (self.incoterm_id.code or self.incoterm_id.name), bottom_info_left_border_format)
            worksheet.write_blank(f'G{current_row}', None, right_only_border_format)
            current_row += 1
        
        # VAT条款
        worksheet.merge_range(f'A{current_row}:G{current_row}', "VAT: ART. 8 1° COMMA LETTERA A D.P.R. 633/72", bottom_info_left_right_format)
        current_row += 1
        
        # PAYMENT
        worksheet.write(f'A{current_row}', f"PAYMENT:", bottom_info_left_border_format)
        worksheet.write(f'B{current_row}', "100% BEFORE SHIPPING", bottom_info_left_border_format)
        worksheet.write_blank(f'G{current_row}', None, right_only_border_format)
        current_row += 1
        
        # 为PAYMENT下一行添加左边框
        worksheet.write_blank(f'A{current_row}', None, left_column_border_format)
        current_row += 1
        
        # 7. 银行信息
        worksheet.write(f'A{current_row}', "BANK NAME:", bottom_info_left_border_format)
        worksheet.write_blank(f'G{current_row}', None, right_only_border_format)
        current_row += 1

        worksheet.write(f'A{current_row}', "Bank Name: Unicredit", bottom_info_left_border_format)
        worksheet.write_blank(f'G{current_row}', None, right_only_border_format)
        current_row += 1

        worksheet.write(f'A{current_row}', "BIC/SWFT: UNCRITM1MN5", bottom_info_left_border_format)
        worksheet.write_blank(f'G{current_row}', None, right_only_border_format)
        current_row += 1

        worksheet.write(f'A{current_row}', "IBAN: IT03L0200812932000106768229", bottom_info_left_border_format)
        worksheet.write_blank(f'G{current_row}', None, right_only_border_format)
        current_row += 1

        worksheet.merge_range(f'A{current_row}:G{current_row}', "Address Bank: Via Marzabotto 75, 41100,Modena", address_bank_border_format)
        
        workbook.close()
        
        # 保存文件
        excel_data = output.getvalue()
        filename = f"Invoice_{invoice_number}_{self.date.strftime('%Y%m%d')}.xlsx"
        
        # 创建附件记录
        attachment = self.env['ir.attachment'].create({
            'name': filename,
            'type': 'binary',
            'datas': base64.b64encode(excel_data),
            'res_model': self._name,
            'res_id': self.id,
        })
        
        # 返回下载链接
        return {
            'type': 'ir.actions.act_url',
            'url': f'/web/content/{attachment.id}?download=true',
            'target': 'self',
        } 